<?php
/**
 * FlickrPhotosetsMysqlDB class file
 * @package FlickrPhotosetsDB
 */

/**
 * Handles MySQL database operations.
 * 
 * @package FlickrPhotosetsDB
 * @author Brad Dougherty <bdougherty@gmail.com>
 * @license http://www.gnu.org/licenses/gpl.html GNU General Public License 3.0
 */
class FP_MysqlDB extends FP_Database {
	
	/**
	 * The mysql database link
	 * @var mysql link
	 */
	private $_db;
	
	/**
	 * The table we are using
	 * @var string
	 */
	private $_db_table;
	
	/**
	 * Constructor.
	 * 
	 * @author Brad Dougherty <bdougherty@gmail.com>
	 * @param string $db_host The hostname for the database server
	 * @param string $db_user The username for the database
	 * @param string $db_pass The password for the database
	 * @param string $db_name The name of the database
	 * @param string $db_table The table to be used
	 * @return boolean True if connected
	 */
	public function __construct($db_host, $db_user, $db_pass, $db_name, $db_table){
		$this->connect_db($db_host, $db_user, $db_pass, $db_name);
		$this->_db_table = Utilities::sqlClean($db_table);
		$this->clean_db();
	}
	
	/**
	 * Destructor closes the mysql connection
	 *
	 * @author Brad Dougherty <bdougherty@gmail.com>
	 */
	public function __destruct() {
		//mysql_close($this->_db);
	}
	
	/**
	 * Clean the database of unneeded/old data.
	 * 
	 * @author Brad Dougherty <bdougherty@gmail.com>
	 */
	private function clean_db() {
		// will primarily clean out log data
	}
	
	/**
	 * Initiates the connection to the database.
	 * 
	 * @author Brad Dougherty <bdougherty@gmail.com>
	 * @param string $host The hostname for the database server
	 * @param string $user The username for the database
	 * @param string $pass The password for the database
	 * @param string $name The name of the database
	 * @return boolean True if connected
	 */
	private function connect_db($host, $user, $pass, $name) {
		$this->_db = mysql_connect($host, $user, $pass) or $this->error(FP_Error::$database_error);
		mysql_select_db($name, $this->_db) or $this->error(FP_Error::$database_error);
		return true;
	}
	
	private function execute_select_query($query) {
		$result = mysql_query($query, $this->_db) or $this->error(FP_Error::$database_error);
		$data = mysql_fetch_assoc($result);
		return $data;
	}
	
	/**
	 * Gets the user's configuration data.
	 * 
	 * @author Brad Dougherty <bdougherty@gmail.com>
	 * @return The configuration array, or false if the user's info wasn't found
	 */
	public function get_info($uid) {
		/*$uid = Utilities::sqlClean($uid);
		$query = "select * from `$this->_db_table` where `facebook_uid` = `$uid`";
		$config = $this->execute_select_query($query);
		*/
		$config = $this->mysql_select_query('', '*', array(
			'facebook_uid' => $uid
		));
		return (count($config) > 0) ? $config : false;
	}
	
	public function get_last_profile_update($uid) {
		return $this->execute_select_query("select * from {$this->_db_table}_log where uid = '{$uid}' and action = 'profile_update' order by id desc limit 1");
	}
	
	/**
	 * Calculates the user's profile album id.
	 * 
	 * @author Brad Dougherty <bdougherty@gmail.com>
	 * @return The aid of the user's profile album
	 */
	public function get_profile_aid($uid) {
		$aid = $this->execute_select_query("select ({$uid} << 32) + (-3 & 0xffffffff) as 'aid'");
		return $aid['aid'];
	}
		
	public function get_recent_tags($minutes = 10) {
		
	}
	
	/**
	 * Get the UID from an NSID.
	 * 
	 * Use this function to lookup a Facebook user from their Flickr ID.
	 * Returns a string that contains the UID if the user was found.
	 * 
	 * @author Brad Dougherty <bdougherty@gmail.com>
	 * @param string $nsid The Flickr ID
	 * @return int The Facebook UID
	 */
	public function get_uid_from_nsid($nsid) {
		$result = $this->mysql_select_query('', 'facebook_uid', array(
			'flickr_nsid' => $nsid
		));
		return $result['facebook_uid'];
	}
	
	/**
	 * Implode an array with the keys.
	 * 
	 * @author adrian at foeder dot de <adrian@foeder.de>
	 * @param string $glue The glue
	 * @param array $array The array to implode
	 * @param string $valwrap The value to put around the values
	 * @return string The imploded array
	 */
	public function implode_with_keys($glue, $array, $valwrap='') {
		foreach($array as $key => $value) {
			$return[] = $key."=".$valwrap.$value.$valwrap;
		}
		return implode($glue, $return);
		//return (count($array) > 1) ? implode($glue, $return) : $return;
	}
	
	/**
	 * Add something to the log.
	 * 
	 * @author Brad Dougherty <bdougherty@gmail.com>
	 * @param int $uid The uid of the adding user
	 * @return boolean True if the query was successful
	 */
	public function log($uid, $action, $success, $text = '') {
		$success = ($success) ? '1' : '0';
		$this->mysql_insert_query('log', array(
			'uid' => $uid,
			'action' => substr($action, 0, 15),
			'success' => $success,
			'text' => $text
		));
		return true;
	}
	
	/**
	 * Generates a mysql delete query
	 *
	 * @author Brad Dougherty <bdougherty@gmail.com>
	 * @param string $table The table extension to use
	 * @param array $where The data for the WHERE clause of the query
	 * @param int $limit The amount to LIMIT
	 * @param boolean $perform_query Whether or not to actually perform the query
	 * @return string The query
	 */
	private function mysql_delete_query($table, $where, $limit = 1, $perform_query = true) {
		
		// Figure out the table
		$table = Utilities::sqlClean($table);
		$table = (empty($table)) ? $this->_db_table : $this->_db_table.'_'.$table;
		
		// Sanitize the data
		$where = Utilities::sqlCleanArray($where);
		$limit = Utilities::sqlClean($limit);
		
		// Assemble the query
		$query = "DELETE FROM $table WHERE ".$this->implode_with_keys(" AND ", $where, "'")." LIMIT $limit";
		
		// Either perform the query or return it
		if ($perform_query) {
			mysql_query($query, $this->_db) or $this->error(FP_Error::$database_error);
		}
		else {
			return $query;
		}
		
	}
	
	/**
	 * Generates a mysql insert query
	 *
	 * @author Brad Dougherty <bdougherty@gmail.com>
	 * @param string $table The table extension to use
	 * @param array $data The data to insert (key is the field)
	 * @param boolean $perform_query Whether or not to actually perform the query
	 * @return string The query
	 */
	private function mysql_insert_query($table, $data, $perform_query = true) {
		
		// Figure out the table
		$table = Utilities::sqlClean($table);
		$table = (empty($table)) ? $this->_db_table : $this->_db_table.'_'.$table;
		
		// Sanitize the data
		$data = Utilities::sqlCleanArray($data);
		
		// Assemble the query
		$query = "INSERT INTO $table (".implode(', ', array_keys($data)).") VALUES ('".implode("', '", $data)."')";
		
		// Either perform the query or return it
		if ($perform_query) {
			mysql_query($query, $this->_db) or $this->error(FP_Error::$database_error);
		}
		else {
			return $query;
		}
		
	}
	
	/**
	 * Mysql select query
	 * 
	 * @author Brad Dougherty <bdougherty@gmail.com>
	 * @param string $table The table extension to use
	 * @param array/string $data The data to get (either a string or an array of strings)
	 * @param array $where The data for the WHERE clause of the query
	 * @param int $limit The amount to LIMIT
	 * @param boolean $perform_query Whether or not to actually perform the query
	 */
	public function mysql_select_query($table, $data, $where, $limit = 1, $perform_query = true) {
		
		// Figure out the table
		$table = Utilities::sqlClean($table);
		$table = (empty($table)) ? $this->_db_table : $this->_db_table.'_'.$table;
		
		// Sanitize the data
		$data = (is_array($data)) ? Utilities::sqlCleanArray($data) : Utilities::sqlClean($data);
		$where = (is_array($where)) ? Utilities::sqlCleanArray($where) : Utilities::sqlClean($where);
		$limit = Utilities::sqlClean($limit);
		
		// Assemble the query
		$query = "SELECT ";
		$query .= (is_array($data)) ? implode(', ', $data) : $data;
		$query .= " FROM $table";
		if (is_array($where)) {
			$query .= ' WHERE '.$this->implode_with_keys(" AND ", $where, "'");
		}
		else if (empty($where)) {
			$query .= ' WHERE 1';
		}
		else {
			$query .= " WHERE $where";
		}
		
		// Either perform the query and return the data or return the string
		if ($perform_query) {
			$result = mysql_query($query, $this->_db) or $this->error(FP_Error::$database_error);
			$data = mysql_fetch_assoc($result);
			return $data;
		}
		else {
			return $query;
		}
		
	}
	
	/**
	 * Generates a mysql update query
	 *
	 * @author Brad Dougherty <bdougherty@gmail.com>
	 * @param string $table The table extension to use
	 * @param array $data The data to insert (key is the field)
	 * @param array $where The data for the WHERE clause of the query
	 * @param int $limit The amount to LIMIT
	 * @param boolean $perform_query Whether or not to actually perform the query
	 * @return string The query
	 */
	private function mysql_update_query($table, $data, $where, $limit = 1, $perform_query = true) {
		
		// Figure out the table
		$table = Utilities::sqlClean($table);
		$table = (empty($table)) ? $this->_db_table : $this->_db_table.'_'.$table;
		
		// Sanitize the data
		$data = Utilities::sqlCleanArray($data);
		$where = Utilities::sqlCleanArray($where);
		$limit = Utilities::sqlClean($limit);
		
		// Assemble the query
		$query = "UPDATE $table SET ".$this->implode_with_keys(", ", $data, "'")." WHERE ".$this->implode_with_keys(" AND ", $where, "'")." LIMIT $limit";
		
		// Either perform the query or return it
		if ($perform_query) {
			mysql_query($query, $this->_db) or $this->error(FP_Error::$database_error);
		}
		else {
			return $query;
		}
		
	}
	
	/**
	 * Removes the user's Flickr information from the database.
	 * 
	 * @author Brad Dougherty <bdougherty@gmail.com>
	 * @param int $uid The Facebook user id
	 * @return boolean True if successfully removed
	 */
	public function remove_flickr_auth($uid) {
		$this->mysql_update_query('', array(
			'flickr_nsid' => '',
			'flickr_token' => ''
		), array(
			'facebook_uid' => $uid
		));
		return (mysql_affected_rows() > 0) ? true : false;
	}
	
	/**
	 * Removes the user from the database.
	 * 
	 * @author Brad Dougherty <bdougherty@gmail.com>
	 * @return boolean True if the query was successful
	 */
	public function remove_user($uid) {
		$this->mysql_delete_query('', array(
			'facebook_uid' => $uid
		));
		return (mysql_affected_rows() > 0) ? true : false;
	}
	
	/**
	 * Saves the current configuration to the database ($config).
	 * 
	 * @author Brad Dougherty <bdougherty@gmail.com>
	 * @param array $config The configuration array
	 * @return boolean True if the query was successful
	 */
	public function save_config($config) {		
		$options = array();
		foreach ($config as $option => $value) {
			if (!in_array($option, array('facebook_uid', 'facebook_session_key', 'infinite_session', 'flickr_nsid', 'flickr_token', 'flickr_auth', 'last_seen', 'beta_user', 'tag_beta_user', 'last_photo', 'photosets'))) {
				$options[$option] = $value;
			}
		}
		
		$this->mysql_update_query('', $options, array(
			'facebook_uid' => $config['facebook_uid']
		));
		
		return (mysql_affected_rows() > 0) ? true : false;
	}
	
	/**
	 * Inserts a user's Facebook ID and session key into the database.
	 * 
	 * @author Brad Dougherty <bdougherty@gmail.com>
	 * @param int $uid The Facebook user id
	 * @param string $facebook_session_key The Facebook session key
	 * @return boolean True if successfully added
	 */
	public function set_facebook_info($uid, $session_key) {
		$this->mysql_insert_query('', array(
			'facebook_uid' => $uid,
			'facebook_session_key' => $session_key
		));
		return (mysql_affected_rows() > 0) ? true : false;
	}
	
	/**
	 * Updates the user's auth level in the database.
	 * 
	 * @author Brad Dougherty <bdougherty@gmail.com>
	 * @param int $uid The Facebook user id
	 * @param string $auth_level The single character string representing the auth level (n, r, w, d)
	 * @return boolean True if successfully updated
	 */
	public function set_flickr_auth($uid, $auth_level) {
		$this->mysql_update_query('', array(
			'flickr_auth' => $auth_level
		), array(
			'facebook_uid' => $uid
		));
		return (mysql_affected_rows() > 0) ? true : false;
	}
	
	/**
	 * Sets the ID of the last photo in the database.
	 * 
	 * @author Brad Dougherty <bdougherty@gmail.com>
	 * @param int $uid The Facebook user id
	 * @param int $pid The photo id
	 * @return boolean True if the query was successful
	 */
	public function set_last_photo($uid, $pid) {
		$this->mysql_update_query('', array(
			'last_photo' => $pid
		), array(
			'facebook_uid' => $uid
		));
		return (mysql_affected_rows() > 0) ? true : false;
	}
	
	/**
	 * Saves a comma separated list of the user's sets in the database.
	 * 
	 * @author Brad Dougherty <bdougherty@gmail.com>
	 * @param int $uid The Facebook user id
	 * @param string $photosets A comma separated list of the set ids
	 * @return boolean True if the query was successful
	 */
	public function set_photosets($uid, $photosets) {
		$this->mysql_update_query('', array(
			'photosets' => $photosets
		), array(
			'facebook_uid' => $uid
		));
		return (mysql_affected_rows() > 0) ? true : false;
	}
	
	/**
	 * Stores the user's Flickr token and NSID in the database.
	 * 
	 * @author Brad Dougherty <bdougherty@gmail.com>
	 * @param int $uid The Facebook user id
	 * @param int $nsid The Flickr user id
	 * @param string $token The token returned from Flickr
	 * @return boolean True if successfully added
	 */
	public function store_token($uid, $nsid, $token) {
		$this->mysql_update_query('', array(
			'flickr_nsid'  => $nsid,
			'flickr_token' => $token,
			'last_seen'    => 'NOW()'
		), array(
			'facebook_uid' => $uid
		));
		return (mysql_affected_rows() > 0) ? true : false;
	}
	
}

?>